package sfms.model;

import java.sql.*;
import java.util.*;
import sfms.util.CustomDate;

@SuppressWarnings("deprecation")
public class Model {
    /**
     * Establishes the database connection.
     * @return true if the database is connected, false otherwise.
     */
    public boolean connect() {
        if (!isConnected) {
            try {
                Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
                conn = DriverManager.getConnection(dbURL); 
                isConnected = true;
            } catch (Exception e) {
                System.err.println(e.getMessage());
                isConnected = false;
            }
        }
        
        return isConnected;
    }

    /**
     * Severs the database connection.
     * @return true if the database is disconnected, false otherwise.
     */
    public boolean disconnect() {

        boolean success = true;

        if (isConnected) {
            try {
                conn.close();
            } catch (Exception e) {
                System.err.println(e.getMessage());
                success = false;
            }

            isConnected = false;
        }

        return success;
    }

    /**
     * Determines whether the model is connected to the database.
     * @return true if the database connection exists.
     */
    public boolean isConnected() {
        return isConnected;
    }

    /**
     * Retrieves a user from the database.
     * @param username The username
     * @return The user.
     */
    public User getUser(String username) {
        User user = null;
        try {
            PreparedStatement stmt = conn.prepareStatement("SELECT * FROM sfms_user WHERE username=?");
            stmt.setString(1, username);

            ResultSet results = stmt.executeQuery();

            if (results.next()) {
                user = new User(
                    results.getString(1),
                    results.getString(2),
                    results.getString(3),
                    results.getString(4),
                    results.getString(5),
                    results.getString(6),
                    results.getBoolean(7),
                    results.getDate(8),
                    results.getString(9)
                );
            }
            results.close();
            stmt.close();
        } catch (Exception e) {
            user = null;
        }

        return user;
    }

    /**
     * Retrieves a user from the database if it contains the specified password.
     * @param username The desired username
     * @param password The users password
     * @return The user entity with a nullified password, or null if it does not exist.
     */
    public User getUser(String username, String passwordHash) {
        User user = getUser(username);
        if (user == null) {
            return null;
        }
        if (user.getPassword().equals(passwordHash)) {
            return user;
        }

        return null;
    }
    
    /**
     * Retrieves all users of a given role type from the database.
     * @param roleType The desired role (User.ADMIN, User.CUSTOMER or User.STAFF).
     * @return The list of User entities that are of the specified role. If the
     * list is empty or an error occurs, null will be returned.
     */
    public List<User> getUsers(String roleType) {
        List<User> users = new ArrayList<User>();
        User u;
        try {
            PreparedStatement stmt = conn.prepareStatement("SELECT * FROM sfms_user WHERE role=?");
            stmt.setString(1, roleType);

            ResultSet results = stmt.executeQuery();

            while (results.next()) {
                u = new User(
                    results.getString(1),
                    results.getString(2),
                    results.getString(3),
                    results.getString(4),
                    results.getString(5),
                    results.getString(6),
                    results.getBoolean(7),
                    results.getDate(8),
                    results.getString(9)
                );

                users.add(u);
            }
            results.close();
            stmt.close();
            
        } catch (Exception e) {
            System.err.println(e.getMessage());
            users = new ArrayList<User>();
        }

        return users;
    }

    /**
     * Adds a completed user record to the database.
     * @param newUser The completed user record.
     * @return true if the insert was a success, false otherwise.
     */
    public boolean addUser(User newUser) {
        boolean success = true;

        try {
            PreparedStatement stmt = conn.prepareStatement("INSERT INTO sfms_user VALUES(?,?,?,?,?,?,?,?,?)");
            stmt.setString(1, newUser.getUsername());
            stmt.setString(2, newUser.getFirstName());
            stmt.setString(3, newUser.getLastName());
            stmt.setString(4, newUser.getEmail());
            stmt.setString(5, newUser.getPhoneNumber());
            stmt.setString(6, newUser.getRole());
            stmt.setBoolean(7, newUser.isActive());
            stmt.setDate(8, newUser.getExpiry());
            stmt.setString(9, newUser.getPassword());

            int count = stmt.executeUpdate();
            if (count != 1) {
                success = false;
            }

            stmt.close();
        } catch (Exception e) {
            System.err.println(e.getMessage());
            success = false;
        }

        return success;
    }
    
    /**
     * Changes the password of a given user, but only if the provided password is correct.
     * @param username The username.
     * @param oldPasswordHash The MD5 hash of the current password (user's attempt).
     * @param newPasswordHash The MD5 hash of the new password.
     * @return 
     */
    public boolean changeUserPassword(String username, String oldPasswordHash, String newPasswordHash) {
        boolean success = true;
        
        try {
            if (!oldPasswordHash.equals(getUser(username).getPassword())) {
                return false;
            }

            PreparedStatement stmt = conn.prepareStatement("UPDATE sfms_user SET password=? WHERE username=? AND password=?");
            stmt.setString(1, newPasswordHash);
            stmt.setString(2, username);
            stmt.setString(3, oldPasswordHash);

            int count = stmt.executeUpdate();
            if (count != 1) {
                success = false;
            }

            stmt.close();
        } catch (Exception e) {
            System.err.println(e.getMessage());
            success = false;
        }

        return success;
    }

    /**
     * Retrieves a license from the database.
     * @param id The desired license 
     * @return The license entity, or null if it does not exist.
     */
    public License getLicense(int id) {
        License license = null;
        try {
            PreparedStatement stmt = conn.prepareStatement("SELECT * FROM license WHERE id=?");
            stmt.setInt(1, id);

            ResultSet results = stmt.executeQuery();

            if (results.next()) {
                license = new License(
                    results.getInt(1),
                    results.getString(2)
                );
            }
            results.close();
            stmt.close();
        } catch (Exception e) {
            license = null;
        }

        return license;
    }

    /**
     * Retrieves a user license from the database.
     * @param username The username
     * @param licenseId The license id
     * @return The UserLicense entity.
     */
    public UserLicense getUserLicense(String username, int licenseId) {
        UserLicense userLicense = null;
        try {
            PreparedStatement stmt = conn.prepareStatement("SELECT * FROM USERLICENSE WHERE username=? AND license_id=?");
            stmt.setString(1, username);
            stmt.setInt(2, licenseId);

            ResultSet results = stmt.executeQuery();

            if (results.next()) {
                userLicense = new UserLicense(
                    getUser(results.getString(1)),
                    getLicense(results.getInt(2)),
                    results.getDate(3)
                );
            }
            results.close();
            stmt.close();
            
            if (userLicense.getUser() == null || userLicense.getLicense() == null) {
                userLicense = null;
            }
            
        } catch (Exception e) {
            userLicense = null;
        }

        return userLicense;
    }
    
    public boolean addUserLicense(UserLicense ul) {
        boolean success = true;

        try {
            PreparedStatement stmt = conn.prepareStatement("INSERT INTO userlicense VALUES(?,?,CURRENT_DATE)");
            stmt.setString(1, ul.getUser().getUsername());
            stmt.setInt(2, ul.getLicense().getId());

            int count = stmt.executeUpdate();
            if (count != 1) {
                success = false;
            }

            stmt.close();
        } catch (Exception e) {
            System.err.println(e.getMessage());
            success = false;
        }

        return success;
    }

    /**
     * Retrieves a facility from the database.
     * @param id The desired facility
     * @return The Facility entity, or null if it does not exist.
     */
    public Facility getFacility(int id) {
        Facility facility = null;
        try {
            PreparedStatement stmt = conn.prepareStatement("SELECT * FROM facility WHERE id=?");
            stmt.setInt(1, id);

            ResultSet results = stmt.executeQuery();

            if (results.next()) {
                facility = new Facility(
                    results.getInt(1),
                    results.getString(2),
                    results.getDate(3),
                    getLicense(results.getInt(4))
                );
            }
            results.close();
            stmt.close();

        } catch (Exception e) {
            facility = null;
        }

        return facility;
    }
    
    public Facility enableFacility(Facility f, boolean enable) {
        
        try {
            PreparedStatement stmt = conn.prepareStatement("UPDATE facility SET not_active=? WHERE id=?");
            if (enable) {
                stmt.setDate(1, null);
                f.setNotActive(null);
            } else {
                CustomDate date = new CustomDate(new java.util.Date(new java.util.Date().getYear(), new java.util.Date().getMonth(), new java.util.Date().getDate()+7, 0, 0, 0));
                stmt.setDate(1, date.getSQLDate()); 
                f.setNotActive(date);
            }

            stmt.setInt(2, f.getId());

            int count = stmt.executeUpdate();
            if (count != 1) {
                return null;
            }

            stmt.close();
        } catch (Exception e) {
            System.err.println(e.getMessage());
            return null;
        }

        return f;
    }
    
    /**
     * Adds a completed facility record to the database. The 
     * @param title The venue title
     * @param licenseText Text for the license, if it has one. Null otherwise
     * @param ts the date and time when the venue is being booked
     * @return true if the insert was a success, false otherwise.
     */

    public Facility addFacility(String title, String licenseText) {
        Integer licenseId = null;
        Integer facilityId = null;

        try {
            PreparedStatement facilityStmt;
            
            if (licenseText != null) {
                facilityStmt = conn.prepareStatement("INSERT INTO Facility(title, not_active, license_id) VALUES(?,?,?)");
                PreparedStatement licenseStmt = conn.prepareStatement("INSERT INTO License(title) VALUES(?)");
                licenseStmt.setString(1, licenseText);
                licenseId = licenseStmt.executeUpdate();
                facilityStmt.setString(1, title);
                facilityStmt.setString(2, "1999-12-5");
                facilityStmt.setInt(3, licenseId);
            }
            else{
                facilityStmt = conn.prepareStatement("INSERT INTO Facility(title, not_active, license_id) VALUES(?,?,NULL)");
                facilityStmt.setString(1, title);
                facilityStmt.setString(2, "1999-12-5");
            }
                    
            facilityId = facilityStmt.executeUpdate();
            if (facilityId != 1) {
                return null;
            }

            facilityStmt.close();
        } catch (Exception e) {
            System.err.println("Error: "+e.getMessage());
            return null;
        }
        if (licenseId != null){
            return new Facility(facilityId, title, null, getLicense(licenseId));
        }
        else{
            return new Facility(facilityId, title, null, null);
        }
    }
    
    /**
     * Retrieves a list of active Facility entities from the database.
     * @return The list of active Facility entities, or null if there was an error
     * or the list is empty.
     */
    public List<Facility> getActiveFacilities(CustomDate d) {
        List<Facility> facilities = new ArrayList<Facility>();
        Facility f;
        try {
            PreparedStatement stmt = conn.prepareStatement("SELECT * FROM facility WHERE not_active IS NULL OR not_active > ?");
            stmt.setDate(1, d.getSQLDate());
            ResultSet results = stmt.executeQuery();

            while (results.next()) {
                f = new Facility(
                    results.getInt(1),
                    results.getString(2),
                    results.getDate(3),
                    getLicense(results.getInt(4))
                );
                
                facilities.add(f);
                System.out.println(f);
            }
            results.close();
            stmt.close();
        } catch (Exception e) {
            facilities = new ArrayList<Facility>();
        }

        return facilities;
    }

    /**
     * Retrieves a list of Facility entities from the database.
     * @return The list of Facility entities, or null if there was an error
     * or the list is empty.
     */
    public List<Facility> getFacilities() {
        List<Facility> facilities = new ArrayList<Facility>();
        Facility f;
        try {
            PreparedStatement stmt = conn.prepareStatement("SELECT * FROM facility");
            ResultSet results = stmt.executeQuery();

            while (results.next()) {
                f = new Facility(
                    results.getInt(1),
                    results.getString(2),
                    results.getDate(3),
                    getLicense(results.getInt(4))
                );
                
                facilities.add(f);
            }
            results.close();
            stmt.close();

        } catch (Exception e) {
            facilities = new ArrayList<Facility>();
        }

        return facilities;
    }

    /**
     * Retrieves a venue from the database.
     * @param id The desired venue.
     * @return The Venue entity, or null if it does not exist.
     */
    public Venue getVenue(int id) {
        Venue venue = null;
        try {
            PreparedStatement stmt = conn.prepareStatement("SELECT * FROM venue where id=?");
            stmt.setInt(1, id);

            ResultSet results = stmt.executeQuery();

            if (results.next()) {
                venue = new Venue(
                    results.getInt(1),
                    results.getString(2),
                    results.getInt(3),
                    getFacility(results.getInt(4))
                );
            }

            if (venue.getFacility() == null) {
                venue = null;
            }
            
            results.close();
            stmt.close();

        } catch (Exception e) {
            venue = null;
        }

        return venue;
    }

    /**
     * Retrieves all venues used by a given facility from the database.
     * @param facilityId The desired venue.
     * @return The list of Venue entities pertaining to the specified facility.
     * If there were no facilities or an error occurs, null will be returned.
     */
    public List<Venue> getVenues(int facilityId) {
        List<Venue> venues = new ArrayList<Venue>();
        Venue v;

        try {
            PreparedStatement stmt = conn.prepareStatement("select * from venue where facility_id=?");
            stmt.setInt(1, facilityId);

            ResultSet results = stmt.executeQuery();

            while (results.next()) {
                v = new Venue(
                    results.getInt(1),
                    results.getString(2),
                    results.getInt(3),
                    getFacility(results.getInt(4))
                );
                
                venues.add(v);
            }

            results.close();
            stmt.close();

        } catch (Exception e) {
            venues = new ArrayList<Venue>();
        }

        return venues;
    }
    
    /**
     * Retrieves all venues used by a given facility from the database.
     * @param facilityId The desired venue.
     * @return The list of Venue entities pertaining to the specified facility.
     * If there were no facilities or an error occurs, null will be returned.
     */
    public List<Venue> getAvailableVenues(int facilityId) {
        List<Venue> venues = new ArrayList<Venue>();
        Venue v;

        try {
            PreparedStatement stmt = conn.prepareStatement(
                    "SELECT DISTINCT title, id, capacity, facility_id, time "
                    + "FROM venue "
                    + "NATURAL JOIN booking "
                    + "WHERE facility_id=?");
            
            stmt.setInt(1, facilityId);

            ResultSet results = stmt.executeQuery();

            
            while (results.next()) {
                v = new Venue(
                    results.getInt(2),
                    results.getString(1),
                    results.getInt(3),
                    getFacility(results.getInt(4))
                );
                
                venues.add(v);
            }

            results.close();
            stmt.close();

        } catch (Exception e) {
            venues = new ArrayList<Venue>();
        }

        return venues;
    }

    /**
     * Adds a completed venue record to the database. The 
     * @param newUser The completed user record.
     * @return true if the insert was a success, false otherwise.
     */
    public boolean addVenue(Venue newVenue) {
        boolean success = true;

        try {
            Facility facility = newVenue.getFacility();
            Integer facilityId = null;
            if (facility != null) {
                facilityId = facility.getId();
            }
            PreparedStatement stmt = conn.prepareStatement("INSERT INTO venue(title, capacity, facility_id) VALUES(?,?,?)");
            stmt.setString(1, newVenue.getTitle());
            stmt.setInt(2, newVenue.getCapacity());
            stmt.setInt(3, facilityId);

            int count = stmt.executeUpdate();
            if (count != 1) {
                success = false;
            }

            stmt.close();
        } catch (Exception e) {
            System.err.println(e.getMessage());
            success = false;
        }

        return success;
    }
    
    /**
     * Removes a venue from the database.
     * @param id The desired venue.
     * @return The true on success, false otherwise.
     */
    public boolean removeVenue(int id) {
        boolean success = true;
        try {
            PreparedStatement stmt = conn.prepareStatement("DELETE FROM venue WHERE id=?");
            stmt.setInt(1, id);

            int result = stmt.executeUpdate();

            if (result != 1) {
                success = false;
            }

            stmt.close();

        } catch (Exception e) {
            success = false;
        }

        return success;
    }
    
    /**
     * Adds a completed booking record to the database. The 
     * @param username User who is entering the record
     * @param venue_id The venue that is being booked
     * @param ts the date and time when the venue is being booked
     * @return true if the insert was a success, false otherwise.
     */
    public boolean addBooking(String username, int venue_id, Timestamp ts) {
        boolean success = true;

        try {
            PreparedStatement stmt = conn.prepareStatement("INSERT INTO Booking(username, venue_id, time) VALUES(?,?,?)");
            stmt.setString(1, username);
            stmt.setInt(2, venue_id);
            stmt.setTimestamp(3, ts);

            int count = stmt.executeUpdate();
            if (count != 1) {
                success = false;
            }

            stmt.close();
        } catch (Exception e) {
            System.err.println(e.getMessage());
            success = false;
        }

        return success;
    }

   /**
     * Retrieves a booking from the database.
     * @param username The username
     * @param venueId The venue id
     * @return The Booking entity.
     */
    public Booking getBooking(String username, int venueId) {
        Booking booking = null;
        try {
            PreparedStatement stmt = conn.prepareStatement("SELECT * FROM booking WHERE username=? AND venue_id=?");
            stmt.setString(1, username);
            stmt.setInt(2, venueId);

            ResultSet results = stmt.executeQuery();

            if (results.next()) {
                booking = new Booking(
                    getUser(results.getString(1)),
                    getVenue(results.getInt(2)),
                    results.getTimestamp(3)
                );
            }
            results.close();
            stmt.close();
            
            if (booking.getUser() == null || booking.getVenue() == null) {
                booking = null;
            }
            
        } catch (Exception e) {
            booking = null;
        }

        return booking;
    }

   /**
     * Retrieves a list of current Bookings for a user from the database.
     * @param username The username
     * @return The list of Booking entities, or null if an error occurred or
     * the list is empty.
     */
    public List<Booking> getCurrentBookings(String username) {
        List<Booking> bookings = new ArrayList<Booking>();
        Booking b;
        try {
            PreparedStatement stmt = conn.prepareStatement("SELECT * FROM booking WHERE username=? AND time > CURRENT_TIMESTAMP");
            stmt.setString(1, username);

            ResultSet results = stmt.executeQuery();

            while (results.next()) {
                b = new Booking(
                    getUser(results.getString(1)),
                    getVenue(results.getInt(2)),
                    results.getTimestamp(3)
                );

                bookings.add(b);
            }
            results.close();
            stmt.close();
            
        } catch (Exception e) {
            bookings = new ArrayList<Booking>();
        }

        return bookings;
    }
    
    public List<Booking> getBookings(java.sql.Date date) {
        List<Booking> bookings = new ArrayList<Booking>();
        Booking b;
        try {
            
            
        Timestamp today = new Timestamp(date.getYear(), date.getMonth(), date.getDate(),0,0,0,0);
        Timestamp tomorrow = new Timestamp(date.getYear(), date.getMonth(), date.getDate(),23,59,59,0);
            
            PreparedStatement stmt = conn.prepareStatement("SELECT * FROM booking WHERE time >= ? AND time < ?");
            stmt.setTimestamp(1, today);
            stmt.setTimestamp(2, tomorrow);

            ResultSet results = stmt.executeQuery();

            while (results.next()) {
                b = new Booking(
                    getUser(results.getString(1)),
                    getVenue(results.getInt(2)),
                    results.getTimestamp(3)
                );

                bookings.add(b);
            }
            results.close();
            stmt.close();

        } catch (Exception e) {
            bookings = new ArrayList<Booking>();
        }

        return bookings;
    }

   /**
     * Removes a booking from the database.
     * @param username The username
     * @param venueId The venue id
     * @return The Booking entity.
     */
    public boolean removeBooking(String username, int venueId) {
        boolean success = true;
        try {
            PreparedStatement stmt = conn.prepareStatement("DELETE FROM booking WHERE username=? AND venue_id=?");
            stmt.setString(1, username);
            stmt.setInt(2, venueId);

            int result = stmt.executeUpdate();

            if (result != 1) {
                success = false;
            }

            stmt.close();

        } catch (Exception e) {
            success = false;
        }

        return success;
    }
    
    public List<Time> getAvailableSlots(Facility f, CustomDate date){
        return this.getAvailableSlots(f, date, false);
    }
    
    public List<Time> getAvailableSlots(Facility f, CustomDate date, boolean admin){

        List<Time> list = new ArrayList<Time>();
        
        for (int i = 0; i < 12; i++){
            Time t = new Time(8+i,30,0);
            list.add(t);
        }

        Timestamp today = new Timestamp(date.getYear(), date.getMonth(), date.getDate(),0,0,0,0);
        Timestamp tomorrow = new Timestamp(date.getYear(), date.getMonth(), date.getDate(),23,59,59,0);
        Booking b;
        try {
            PreparedStatement stmt = conn.prepareStatement(
                    "SELECT DISTINCT time, capacity FROM booking "
                    + "NATURAL JOIN venue "
                    + "WHERE capacity <= ("
                    + "SELECT count(time) from BOOKING "
                    + "WHERE venue_id = venue.id "
                    + "AND time >= ? AND time < ? "
                    + ") AND time >= ? AND time < ? AND time > CURRENT_TIMESTAMP");

            stmt.setTimestamp(1, today);
            stmt.setTimestamp(2, tomorrow);
            stmt.setTimestamp(3, today);
            stmt.setTimestamp(4, tomorrow);

            ResultSet results = stmt.executeQuery();

            while (results.next()) {

                System.out.println("Result: " + results.getInt(2));
                Timestamp result = results.getTimestamp(1);
                Time t = new Time(result.getHours(), result.getMinutes(), 0);
                        System.out.println("Searching: " + t);
                if (!admin){
                    for (Time tmpT: list){
                        System.out.println("tmpT: " + tmpT);
                        if (tmpT.getHours() == t.getHours()){
                            System.out.println("Found: " + tmpT+ " - removing.");
                            list.remove(tmpT);
                            break;
                        }
                    }
                }
            }
            results.close();
            stmt.close();
            
        } catch (Exception e) {
            System.out.println(e.getMessage());
            list = new ArrayList<Time>();
        }
        
        return list;
    }

        public boolean updateUser(User user) {
        boolean success = true;
        
        try {
            PreparedStatement stmt = conn.prepareStatement("UPDATE sfms_user "
                                                            + "SET fname=?, lname=?, email=?, phone=?, role=?, password=?, is_active=? " 
                                                            + "WHERE username=?");
            
            stmt.setString(1, user.getFirstName());
            stmt.setString(2, user.getLastName());
            stmt.setString(3, user.getEmail());
            stmt.setString(4, user.getPhoneNumber());
            stmt.setString(5, user.getRole());
            stmt.setString(6, user.getPassword());
            if (user.isActive()){
                stmt.setInt(7,1);
            }
            else{
                stmt.setInt(7,0);
            }
            stmt.setString(8, user.getUsername());

            int count = stmt.executeUpdate();
            if (count != 1) {
                success = false;
            }

            stmt.close();
        } catch (Exception e) {
            System.err.println(e.getMessage());
            success = false;
        }

        return success;
    }
    
    public boolean removeUser(String username) {
        boolean success = true;
        try {
            PreparedStatement stmt = conn.prepareStatement("DELETE FROM sfms_user where username=?");
            stmt.setString(1, username);

            int result = stmt.executeUpdate();

            if (result != 1) {
                success = false;
            }

            stmt.close();

        } catch (Exception e) {
            success = false;
        }

        return success;
    }
    
    public boolean removeFacility(int facilityId) {
        boolean success = true;
        try {
            PreparedStatement stmt = conn.prepareStatement("DELETE from FACILITY WHERE id=?");
            stmt.setInt(1, facilityId);

            int result = stmt.executeUpdate();

            if (result != 1) {
                success = false;
            }

            stmt.close();

        } catch (Exception e) {
            success = false;
        }

        return success;
    }

    private String dbURL = "jdbc:derby://localhost:1527/sfms;user=root;password=sfmsroot";
    private Connection conn = null;
    private boolean isConnected = false;
}
